package persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.QueryResult;
import model.VadeMecumMultimedia;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Expression;

import exception.BDException;

public class VadeMecumMultimediaDAO {
	
	private static VadeMecumMultimediaDAO instance;
	
	private VadeMecumMultimediaDAO() { }
	
	public static VadeMecumMultimediaDAO getInstance() {
		instance = instance == null ? new VadeMecumMultimediaDAO() : instance;		
		
		return instance;
	}
	
	public boolean saveOrUpdate(VadeMecumMultimedia file) {
		PreparedStatement stm = null;
		if (file.getId() == null) { //insert
			stm = createInsertStatement(file);
		} else { //update
			stm = createUpdateStatement(file);
		}
		
		System.out.println(stm.toString());
		
		try {
			stm.executeUpdate();
			stm.close();
		} catch (SQLException e) {
			System.err.println("Erro ao executar SQL");
			e.printStackTrace();
			return false;
		}

		return true;
	}	
	
	private PreparedStatement createInsertStatement(VadeMecumMultimedia file) {
		PreparedStatement stm = null;
		try {
			if (file.getmetadata() == null) {
				if (file.getLocation() == null) { 
					stm = DBConnectionUtil.getConnection().prepareStatement("INSERT INTO vademecummultimedia (url, filetype) " +
							"VALUES (?, ?)");
					stm.setString(1, file.getfileurl());
					stm.setString(2, file.getfiletype());
				} else { 
					stm = DBConnectionUtil.getConnection().prepareStatement("INSERT INTO vademecummultimedia (url, filetype, location) " +
							"VALUES (?, ?, GeometryFromText(?))");
					stm.setString(1, file.getfileurl());
					stm.setString(2, file.getfiletype());
					stm.setString(3, file.getLocation().toString());
				}
			} else {
				if (file.getLocation() == null) { 
					stm = DBConnectionUtil.getConnection().prepareStatement("INSERT INTO vademecummultimedia (url, filetype, metadata) " +
							"VALUES (?, ?,\'" + file.getmetadata() + "\')");
					stm.setString(1, file.getfileurl());
					stm.setString(2, file.getfiletype());
				} else { 
					stm = DBConnectionUtil.getConnection().prepareStatement("INSERT INTO vademecummultimedia (url, filetype, location) " +
							"VALUES (?, ?,\'" + file.getmetadata() + "\', GeometryFromText(?))");
					stm.setString(1, file.getfileurl());
					stm.setString(2, file.getfiletype());
					stm.setString(3, file.getLocation().toString());
				}
			}
		} catch (SQLException e) {
			System.err.println("Erro ao inserir no BD");
			e.printStackTrace();			
		}
		return stm;
	}
	
	private PreparedStatement createUpdateStatement(VadeMecumMultimedia file) {
		PreparedStatement stm = null;
		try {
			if (file.getmetadata() == null) {
				if (file.getLocation() == null) {
					stm = DBConnectionUtil.getConnection().prepareStatement("UPDATE vademecummultimedia SET url=? WHERE id=?");
					stm.setString(1, file.getfileurl());
					stm.setInt(2, file.getId());
				} else {
					stm = DBConnectionUtil.getConnection().prepareStatement("UPDATE vademecummultimedia SET url=?, location=GeometryFromText(?) " +
					"WHERE id=? ");
					stm.setString(1, file.getfileurl());
					stm.setString(2, file.getLocation().toString());
					stm.setInt(3, file.getId());
				}
			} else {
				if (file.getLocation() == null) {
					stm = DBConnectionUtil.getConnection().prepareStatement("UPDATE vademecummultimedia SET url=?, metadata=\'" + file.getmetadata() + "\'" +
					"WHERE id=?");
					stm.setString(1, file.getfileurl());
					stm.setInt(2, file.getId());
				} else {
					stm = DBConnectionUtil.getConnection().prepareStatement("UPDATE vademecummultimedia SET url=? metadata=\'" + file.getmetadata() +"\', location=GeometryFromText(?) " +
					"WHERE id=? ");
					stm.setString(1, file.getfileurl());
					stm.setString(2, file.getLocation().toString());
					stm.setInt(3, file.getId());
				}
			}
		} catch (SQLException e) {
			System.err.println("Erro ao atualizar BD");
			e.printStackTrace();
		}
		return stm;
	}

	public void remove(VadeMecumMultimedia file) {
		try {
			PreparedStatement stmt = DBConnectionUtil.getConnection().prepareStatement("DELETE FROM vademecummultimedia WHERE id=?");  
			stmt.setInt(1, file.getId());  
			stmt.execute();  
			stmt.close(); 
		} catch (SQLException sqle) {
			System.err.println("Erro ao tentar remover objeto");
			sqle.printStackTrace();
		}
	}	
	
	public VadeMecumMultimedia getFilebyId(Integer key) {
		try {
			PreparedStatement stmt = DBConnectionUtil.getConnection().prepareStatement("select * from vademecummultimedia where id=?");
			stmt.setInt(1, key);

			System.out.println(stmt);

			ResultSet rs = stmt.executeQuery();  

			if(!rs.next()) {
				return null;  
			}

			VadeMecumMultimedia file = new VadeMecumMultimedia();  
			file.setId(rs.getInt("id"));  
			file.setfileurl(rs.getString("url"));  
			file.setfiletype(rs.getString("filetype"));  
			file.setmetadata(rs.getString("metadata"));  

			rs.close();  

			stmt.close();  

			return file;
		} catch (SQLException sqle) {
			System.err.println("Erro ao tentar recuperar objeto");
			sqle.printStackTrace();
		}
		
		return null;
	}
	
	public List<VadeMecumMultimedia> getAllFiles() {
		List<VadeMecumMultimedia> list = new ArrayList<VadeMecumMultimedia>();  

		try {
			PreparedStatement stmt = DBConnectionUtil.getConnection().prepareStatement("SELECT * FROM vademecummultimedia");  
			ResultSet rs = stmt.executeQuery();  

			while (rs.next()) {  
				VadeMecumMultimedia file = new VadeMecumMultimedia();  
				file.setId(rs.getInt("id"));  
				file.setfileurl(rs.getString("url"));  
				file.setfiletype(rs.getString("filetype"));  
				file.setmetadata(rs.getString("metadata"));    

				list.add(file);  
			}  

			rs.close();  
			stmt.close();	

		} catch (SQLException sqle) {
			System.err.println("Erro ao tentar recuperar objeto(s)");
			sqle.printStackTrace();
		} 			
		
		return list;		
	}
	
	
	//TODO falta montar a parte GEOMETRY da consulta!
	public List<VadeMecumMultimedia> getTheseFiles(String pathExpression, String locationPoint, String radius) {
		System.out.println(pathExpression);
	

		List<VadeMecumMultimedia> results = new ArrayList<VadeMecumMultimedia>();
//		String sql = "SELECT f.url, xpath(\'" + pathExpression + "\', metadata) FROM vademecummultimedia f"; TODO funcionando
		
		String sql = "SELECT f.id, f.url FROM vademecummultimedia f WHERE " + pathExpression;
		
		try {
			PreparedStatement stm = DBConnectionUtil.getConnection().prepareStatement(sql);
//			stm.setString(1, pathExpression);
			System.out.println(stm.toString());
			ResultSet rs = stm.executeQuery();
			while (rs.next()) {
				VadeMecumMultimedia f = new VadeMecumMultimedia(rs.getString(2), null, null);
				f.setId(Integer.parseInt(rs.getString(1)));
				results.add(f);
			}
			System.out.println("Resultado: " + results);
			stm.close();
		} catch (SQLException e) {
			System.err.println("Erro ao executar query");
			e.printStackTrace();
		}
		return results;
	}
	
	public QueryResult getFileProperty(int fileId, String fileProperty) throws BDException {
		
		String sql = "SELECT xpath(\'" + fileProperty + "\', metadata) FROM vademecummultimedia f WHERE f.id = " + fileId;
		
		try {
			PreparedStatement stm = DBConnectionUtil.getConnection().prepareStatement(sql);
			ResultSet rs = stm.executeQuery();
			rs.next();
			System.out.println("RS -- " + rs.getString(1));
			QueryResult result = new QueryResult(fileId+"", rs.getString(1));				
			stm.close();
			return result;
		} catch (SQLException e) {
			System.err.println("Erro ao executar query");			
			e.printStackTrace();
			//TODO esse tratamento deve ser feito de maneira diferente!
			throw new BDException("This property can not be returned.");
		}
	}
}
